package com.ipan.poi.excel.util;

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.Date;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellUtil;

import com.ipan.poi.utils.PoiBeanHelper;

/**
 * Excel工具类；
 * 
 * @author iPan
 * @version 2013-09-20
 */
public final class XlsUtils {
	private XlsUtils() {
	}

	/**
	 * 获得有效的行标（注：第一列必须不能为空），从0开始；
	 */
	public static int getValidRowIndex(Sheet sheet) {
		int lastRow = sheet.getLastRowNum();
		int rowIndex = 0;
		for (rowIndex = lastRow; rowIndex > 0; --rowIndex) {
			Cell cell = sheet.getRow(rowIndex).getCell(0);
			if (cell == null) {
				continue;
			}
//			int cellType = cell.getCellType();
//			if (Cell.CELL_TYPE_BLANK != cellType) {
//				break;
//			}
			CellType cellType = cell.getCellTypeEnum(); // 2018-05-07修改
			if (CellType.BLANK != cellType) {
				break;
			}
		}
		return rowIndex;
	}

	/**
	 * 获取单元格内容； 单元格类型转换为java类型，与setCellValue对应；
	 */
	public static Object getCellValue(Cell cell) {
		Object result = null;
		if (cell == null) {
			return result;
		}

//		int type = cell.getCellType();
//		switch (type) {
//		case Cell.CELL_TYPE_NUMERIC:
//			double value = cell.getNumericCellValue();
//			if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
//				result = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
//			} else {
//				result = value;
//			}
//			break;
//		case Cell.CELL_TYPE_STRING:
//			result = cell.getStringCellValue();
//			break;
//		case Cell.CELL_TYPE_FORMULA:
//			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
//			result = cell.getNumericCellValue();
//			break;
//		case Cell.CELL_TYPE_BLANK:
//			break;
//		case Cell.CELL_TYPE_BOOLEAN:
//			result = cell.getBooleanCellValue();
//			break;
//		case Cell.CELL_TYPE_ERROR:
//			break;
//		}
		CellType type = cell.getCellTypeEnum();
		if (CellType.NUMERIC == type) {
			double value = cell.getNumericCellValue();
			if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
				result = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
			} else {
				result = value;
			}
		} else if (CellType.STRING == type) {
			result = cell.getStringCellValue();
		} else if (CellType.FORMULA == type) {
			cell.setCellType(CellType.NUMERIC);
			result = cell.getNumericCellValue();
		} else if (CellType.BOOLEAN == type) {
			result = cell.getBooleanCellValue();
		}
		return result;
	}

	/**
	 * 设置单元格内容； 与getCellValue对应，value与type必须一致；
	 */
	public static void setCellValue(Cell cell, Object value, CellType type) {
//		switch (type) {
//		case Cell.CELL_TYPE_NUMERIC:
//			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
//			if (value instanceof Date) {
//				cell.setCellValue((Date) value);
//			} else {
//				cell.setCellValue((Double) value);
//			}
//			break;
//		case Cell.CELL_TYPE_STRING:
//			cell.setCellType(Cell.CELL_TYPE_STRING);
//			cell.setCellValue((String) value);
//			break;
//		case Cell.CELL_TYPE_FORMULA:
//			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
//			cell.setCellValue((Double) value);
//			break;
//		case Cell.CELL_TYPE_BLANK:
//			cell.setCellType(Cell.CELL_TYPE_BLANK);
//			break;
//		case Cell.CELL_TYPE_BOOLEAN:
//			cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
//			cell.setCellValue((Boolean) value);
//			break;
//		case Cell.CELL_TYPE_ERROR:
//			cell.setCellType(Cell.CELL_TYPE_ERROR);
//			cell.setCellValue((String) value);
//			break;
//		}
		
		if (CellType.NUMERIC == type) {
			cell.setCellType(CellType.STRING);
			if (value instanceof Date) {
				cell.setCellValue((Date) value);
			} else {
				cell.setCellValue((Double) value);
			}
		} else if (CellType.STRING == type) {
			cell.setCellType(CellType.STRING);
			cell.setCellValue((String) value);
		} else if (CellType.FORMULA == type) {
			cell.setCellType(CellType.FORMULA);
			cell.setCellValue((Double) value);
		} else if (CellType.BLANK == type) {
			cell.setCellType(CellType.BLANK);
		} else if (CellType.BOOLEAN == type) {
			cell.setCellType(CellType.BOOLEAN);
			cell.setCellValue((Boolean) value);
		} else if (CellType.ERROR == type) {
			cell.setCellType(CellType.ERROR);
			cell.setCellValue((String) value);
		}
	}

	/**
	 * 获取单元格内容（填充null为空字符串）
	 */
	public static Object getConvertCellValue(Cell cell) {
		Object value = getCellValue(cell);
		return (value != null) ? value : "";
	}

	/**
	 * 获取单元格内容，填充为字符串（null填充空字符串）；
	 */
	public static String getCellValueToString(Cell cell) {
		Object value = getCellValue(cell);
		return (value != null) ? value.toString() : "";
	}

	/**
	 * 获取单元格类型（-1指不存在）
	 */
	public static CellType getCellType(Cell cell) {
//		return (cell != null) ? cell.getCellType() : -1;
		return (cell != null) ? cell.getCellTypeEnum() : null;
	}

	/**
	 * 创建一个带类型的单元格； 类型根据实体类的字段类型来匹配；
	 */
	public static Cell createCellByEntity(Object bean, String fieldName, String format, Row row, int index, CellStyle style) {
		Class<?> fieldType = PoiBeanHelper.getBeanType(bean, fieldName);
		Object fieldValue = PoiBeanHelper.getBeanValue(bean, fieldName);
		return createCell(fieldValue, format, row, index, fieldType, style);
	}

	/**
	 * 创建一个带类型的单元格；
	 */
	public static Cell createCell(Object value, String format, Row row, int index, Class<?> fieldType, CellStyle style) {
		Cell cell = null;
		Workbook workbook = row.getSheet().getWorkbook();
		if (style == null) {
			style = workbook.createCellStyle();
		}

		// 空值
		if (value == null) {
//			cell = row.createCell(index, Cell.CELL_TYPE_STRING);
			cell = row.createCell(index, CellType.STRING);
			cell.setCellValue((String) value);
			// 字符串
		} else if (CharSequence.class.isAssignableFrom(fieldType)) {
//			cell = row.createCell(index, Cell.CELL_TYPE_STRING);
			cell = row.createCell(index, CellType.STRING);
			cell.setCellValue(value.toString());
			// 数字
		} else if (Number.class.isAssignableFrom(fieldType) || Integer.TYPE == fieldType || Short.TYPE == fieldType || Byte.TYPE == fieldType
				|| Long.TYPE == fieldType || Float.TYPE == fieldType || Double.TYPE == fieldType || BigDecimal.class == fieldType) {
//			cell = row.createCell(index, Cell.CELL_TYPE_NUMERIC);
			cell = row.createCell(index, CellType.NUMERIC);
			double val = Double.parseDouble(value.toString());
			cell.setCellValue(val);
			if (format != null && format.length() > 0) {
				style.setDataFormat(workbook.createDataFormat().getFormat(format));
			}
			// 布尔
		} else if (Boolean.class == fieldType || Boolean.TYPE == fieldType) {
//			cell = row.createCell(index, Cell.CELL_TYPE_BOOLEAN);
			cell = row.createCell(index, CellType.BOOLEAN);
			cell.setCellValue(Boolean.parseBoolean(value.toString()));
			// 日期时间
		} else if (Date.class == fieldType || Timestamp.class == fieldType) {
//			cell = row.createCell(index, Cell.CELL_TYPE_NUMERIC);
			cell = row.createCell(index, CellType.NUMERIC);
			cell.setCellValue((Date) value);
			if (format != null && format.length() > 0) {
				style.setDataFormat(workbook.createDataFormat().getFormat(format));
			}
			// 其他类型
		} else {
//			cell = row.createCell(index, Cell.CELL_TYPE_STRING);
			cell = row.createCell(index, CellType.STRING);
			cell.setCellValue(value.toString());
		}

		// 设置单元格样式
		cell.setCellStyle(style);
		return cell;
	}

//	public static void copyRow(Row srcRow, Row destRow) {
//		destRow.setHeight(srcRow.getHeight());
//		for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
//			Cell srcCell = srcRow.getCell(j);
//			if (srcCell != null) {
//				Cell destCell = destRow.createCell(j);
//				copyCell(srcCell, destCell);
//			}
//		}
//	}
//	public static void copyCell(Cell srcCell, Cell destCell) {
//		CellStyle cellStyleClazz = destCell.getSheet().getWorkbook().createCellStyle(); // createCellStyle有限制，不可以无限创建！
//		if (cellStyleClazz instanceof XSSFCellStyle) {
//			XSSFCellStyle newCellStyle = (XSSFCellStyle) cellStyleClazz;
//			newCellStyle.cloneStyleFrom(srcCell.getCellStyle());
//			newCellStyle.setDataFormat(srcCell.getCellStyle().getDataFormat());
//			// Issue with border style, so added this code
//			// newCellStyle.getCoreXf().unsetBorderId();
//			// newCellStyle.getCoreXf().unsetFillId();
//			// -------------
//			destCell.setCellStyle(newCellStyle);
//		}
//
//		if (srcCell.getCellType() == Cell.CELL_TYPE_BLANK) {
//			destCell.setCellType(Cell.CELL_TYPE_BLANK);
//		} else if (srcCell.getCellType() == Cell.CELL_TYPE_STRING) {
//			destCell.setCellValue(srcCell.getStringCellValue());
//		} else if (srcCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
//			destCell.setCellValue(srcCell.getNumericCellValue());
//		} else if (srcCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
//			destCell.setCellValue(srcCell.getBooleanCellValue());
//		} else if (srcCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
//			destCell.setCellFormula(srcCell.getCellFormula());
//		} else if (srcCell.getCellType() == Cell.CELL_TYPE_ERROR) {
//			destCell.setCellErrorValue(srcCell.getErrorCellValue());
//		}
//	}
//	public static void removeRows(Sheet destSheet) {
//		if (null != destSheet) {
//			for (int i = destSheet.getFirstRowNum(); i <= destSheet.getLastRowNum(); i++) {
//				Row row = destSheet.getRow(i);
//				if (null != row) {
//					destSheet.removeRow(row);
//				}
//			}
//		}
//	}
//	public static void addRows(Sheet destSheet, int totalRowCount) {
//		if (null != destSheet) {
//			for (int i = 0; i <= totalRowCount; i++) {
//				destSheet.createRow(i);
//			}
//		}
//	}
//	public static void copyMergedRegion(Sheet srcSheet, Sheet destSheet) {
//		for (int i = 0; i < srcSheet.getNumMergedRegions(); i++) {
//			destSheet.addMergedRegion(srcSheet.getMergedRegion(i));
//		}
//	}
//	/**
//	 * 
//	 * Copy a sheet from one workbook to another workbook. The method supports
//	 * only two similar type of workbooks Xlsx type
//	 * 
//	 * @param srcSheet
//	 * @param destSheet
//	 */
//	public static void copySheet(Sheet srcSheet, Sheet destSheet) {
//		removeRows(destSheet);
//		addRows(destSheet, srcSheet.getLastRowNum());
//		copyMergedRegion(srcSheet, destSheet);
//		for (int i = srcSheet.getFirstRowNum(); i <= srcSheet.getLastRowNum(); i++) {
//			Row srcRow = srcSheet.getRow(i);
//			if (null == srcRow) {
//				destSheet.createRow(i);
//			} else {
//				Row destRow = destSheet.createRow(i);
//				copyRow(srcRow, destRow);
//			}
//		}
//	}

	/**
	 * 从一个Sheet的一行复制到另一个Sheet的一行（带有单元格样式）；
	 */
	public static void copyRow(Row fromRow, Row toRow) {
		// 设置行高
		toRow.setHeight(fromRow.getHeight());
		// 复制单元格
		int cellNum = fromRow.getLastCellNum();
		for (int i = 0; i < cellNum; ++i) {
			Cell fromCell = fromRow.getCell(i);
//			int fromType = getCellType(fromCell);
			CellType fromType = getCellType(fromCell);
			fromType = (fromType != null) ? fromType : CellType.STRING;
			Object fromValue = getCellValue(fromCell);
			Cell toCell = CellUtil.getCell(toRow, i);
			setCellValue(toCell, fromValue, fromType);
//			if (fromCell != null) { // TODO: 取消设置样式 2016-04-13
//				CellStyle fromStyle = fromCell.getCellStyle();
				// 注意：这种写法是错误的，必须按照下面重新new一个CellStyle！
				// toRow.getCell(i).getCellStyle().cloneStyleFrom(fromStyle);
				// 注意：下面的写法能够解决问题，但是createCellStyle方法调用受限制，大量使用会抛异常！
//				 CellStyle toStyle = toRow.getSheet().getWorkbook().createCellStyle();
//				 toStyle.cloneStyleFrom(fromStyle);
//				 toRow.getCell(i).setCellStyle(toStyle);
//			}
		}
	}
	
	/**
	 * 从一个Sheet的一行复制到另一个Sheet的一行（带有单元格样式）；
	 * 单元格样式不能无限制创建也不能使用cloneStyleFrom来克隆（官方实现有点问题），所以，由外部控制单元格样式；
	 */
	public static void copyRow2(Row fromRow, Row toRow, CellStyle cellStyle) {
		// 设置行高
		toRow.setHeight(fromRow.getHeight());
		// 复制单元格
		int cellNum = fromRow.getLastCellNum();
		for (int i = 0; i < cellNum; ++i) {
			Cell fromCell = fromRow.getCell(i);
//			int fromType = getCellType(fromCell);
			CellType fromType = getCellType(fromCell);
//			fromType = (fromType != -1) ? fromType : Cell.CELL_TYPE_STRING;
			fromType = (fromType != null) ? fromType : CellType.STRING;
			Object fromValue = getCellValue(fromCell);
			Cell toCell = CellUtil.getCell(toRow, i);
			setCellValue(toCell, fromValue, fromType);
			toCell.setCellStyle(cellStyle); // 从外部传入一个样式
		}
	}

}
